import numpy as np
import xlrd
#from win32com.client import Dispatch

#--------------------------------------------------------------------------
#MAIN
if __name__ == "__main__":
    print '============='
    # open excel application module
    #xlsApp=Dispatch("Excel.Application")
    year = 'Co9293'
    fileFloder = 'C:/_DATA/migration89_08/COUNTY Migration/'+year+'/'+year
    
    row = 0
    state = ['al', 'ak', 'az', 'ar', 'ca', 'co', 'ct', 'de', 'fl', 'ga', 'hi', 'id', 'il', 'in', 'ia', 'ks', 'ky', 'la', 'me', 'md', 'ma', 'mi', 'mn', 'ms', 'mo', 'mt', 'ne', 'nv', 'nh', 'nj', 'nm', 'ny', 'nc', 'nd', 'oh', 'ok', 'or', 'pa', 'ri', 'sc', 'sd', 'tn', 'tx', 'ut', 'vt', 'va', 'wa', 'wv', 'wi', 'wy']
    #state = ['hi']
    inout = ['i','o']
    #fileFloder = 'C:/_DATA/migration89_08/COUNTY Migration/Co0001_r/Co0001r_excel/Co0001'
    fileFloder = 'C:/_DATA/migration89_08/COUNTY Migration/CO9293/Co9293_excel/Co9293'
    
    state = ['al']
    inout = ['i']
    for io in inout:
        if io == 'i':
            filePath = fileFloder + 'in'+'_excel/'
        else:
            filePath = fileFloder + 'out'+'_excel/'
            
        totalOutput = np.array([])
        
        for st in state:
            print st, io
            inputfile = filePath + 'C9293' + st + io +'.xls'

            xlsBook = xlrd.open_workbook(inputfile)
            xlsSheet = xlsBook.sheet_by_index(0)

            output = []
            NRows = xlsSheet.nrows
            NRows = 10
            for i in range(8, NRows):
                temp = xlsSheet.row_values(i)
                print temp
                if temp[6] == 'd':
                    continue
                output.append([708, int(temp[0]), int(temp[1]), int(temp[2]), int(temp[3]), int(temp[6]), int(temp[7]), int(temp[8])])
                #output.append([xlsSheet.Cells(i,1), xlsSheet.Cells(i,2), xlsSheet.Cells(i,3), xlsSheet.Cells(i,4), xlsSheet.Cells(i,7), xlsSheet.Cells(i,8), xlsSheet.Cells(i,9)])

            totalOutput = np.append(totalOutput, output)
            #output = np.array(output)
            #print output
            
            outputPath = inputfile[:-4] + '.csv'
            np.savetxt(outputPath, output, delimiter=',', fmt = '%10.5f')
        outputPath = inputfile[:-7] + io +'.csv'
        #totalOutput = np.array(totalOutput)
        totalOutput.shape = (-1, 8)
        #np.savetxt(outputPath, totalOutput, delimiter=',', fmt = '%10.5f')
    print 'Done!'
        

